package com.ztesoft.report.utils.excel;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.OutputStream;
import java.net.URLEncoder;

/**
 * @author y3tu
 * @date 2018/4/3
 */
public class POIUtils {


    public static final String XLSX_SUFFIX = ".xlsx";

    public static final String XLSX_CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    private static final int mDefaultRowAccessWindowSize = 100;


    public static Workbook newWorkbook() {
        return new HSSFWorkbook();
    }

    public static Sheet newSheet(Workbook wb, String sheetName) {
        return (HSSFSheet) wb.createSheet(sheetName);
    }

    public static Row newRow(Sheet sheet, int index) {
        return (HSSFRow) sheet.createRow(index);
    }

    public static Cell newCell(Row row, int index) {
        return (HSSFCell) row.createCell(index);
    }

    /**
     * 设定单元格宽度 (手动/自动)
     *
     * @param sheet 工作薄对象
     * @param index 单元格索引
     * @param width 指定宽度,-1为自适应
     * @param value 自适应需要单元格内容进行计算
     */
    public static void setColumnWidth(SXSSFSheet sheet, int index, short width, String value) {
        if (width == -1 && value != null && !"".equals(value)) {
            sheet.setColumnWidth(index, (short) (value.length() * 512));
        } else {
            width = width == -1 ? 200 : width;
            sheet.setColumnWidth(index, (short) (width * 35.7));
        }
    }

    public static void writeByLocalOrBrowser(HttpServletResponse response, String fileName, Workbook wb,
                                             OutputStream out) throws Exception {
        ZipSecureFile.setMinInflateRatio(0L);
        if (response != null) {
            // response对象不为空,响应到浏览器下载
            response.setContentType(XLSX_CONTENT_TYPE);
            response.setHeader("Content-disposition", "attachment; filename="
                    + URLEncoder.encode(String.format("%s%s", fileName, XLSX_SUFFIX), "UTF-8"));
            if (out == null) {
                out = response.getOutputStream();
            }
        }
        wb.write(out);
        out.flush();
        out.close();
    }

    public static void checkExcelFile(File file) {
        if (file == null || !file.exists()) {
            throw new IllegalArgumentException("excel文件不存在.");
        }

        checkExcelFile(file.getAbsolutePath());
    }

    public static void checkExcelFile(String file) {
        if (!file.endsWith(XLSX_SUFFIX)) {
            throw new IllegalArgumentException("抱歉,目前ExcelKit仅支持.xlsx格式的文件.");
        }
    }

    /**
     * 合并单元格设置边框
     *
     * @param cellRangeTitle
     * @param sheet
     */
    public static void setBorderForMergeCell(CellRangeAddress cellRangeTitle, Sheet sheet) {
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeTitle, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeTitle, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeTitle, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeTitle, sheet);
    }


}
